package com.pic.mgt.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.RowMapper;

import com.pic.mgt.manager.terminal.BasicTerminalInfoBean;
import com.pic.mgt.utils.SqlScriptUtils;

public class TerminalMgtDao extends BasicDao implements ITerminalMgtDao {
	// 資料表名稱
	private final String tableName = "terminal_mgt";
	
	private final String insertBasicSqlScript;
	private final String findAllTerminalBasicSqlScript;
	private final String findTerminalBasicByTerminalIdSqlScript;
	private final String deleteByTerminalSqlScript;
	
	public TerminalMgtDao() {
		super();
		
		// 建立 Insert Basic 的 Sql Scritp
		insertBasicSqlScript = createInsertBasicSQLScript();
		
		// 建立 find termainl basic by terminal id 的 Sql Scritp
		findTerminalBasicByTerminalIdSqlScript = createFindTerminalBasicByTerminalIdSqlScript();
		
		// 建立 find all termainl basic 的 Sql Scritp
		findAllTerminalBasicSqlScript = createFindAllTerminalBasicSqlScript();
		
		// 建立 delete by terminal 的 Sql Scritp
		deleteByTerminalSqlScript = createDeleteByTerminalSqlScript();
	}
	
	private String createInsertBasicSQLScript() {
		Vector<String> insertFieldsVector =
								new Vector<String>();
		
		insertFieldsVector.add(_terminalId);
		insertFieldsVector.add(_effectiveStartTime);
		insertFieldsVector.add(_effectiveEndTime);
		
		return
			SqlScriptUtils.createNamedParamInsertScript(
										tableName,
										insertFieldsVector);
	}
	
	private String createFindTerminalBasicByTerminalIdSqlScript() {
		Vector<String> selectFieldsVector =
								new Vector<String>();

		selectFieldsVector.add(_terminalId);
		selectFieldsVector.add(_clientType);
		selectFieldsVector.add(_effectiveStartTime);
		selectFieldsVector.add(_effectiveEndTime);
		
		return SqlScriptUtils.createNamedParamQueryScriptWithSingleCondition(
				tableName, selectFieldsVector, _terminalId);
	}
	
	private String createFindAllTerminalBasicSqlScript() {
		Vector<String> selectFieldsVector =
								new Vector<String>();
		
		selectFieldsVector.add(_terminalId);
		selectFieldsVector.add(_clientType);
		selectFieldsVector.add(_effectiveStartTime);
		selectFieldsVector.add(_effectiveEndTime);
		
		return
			SqlScriptUtils.createNamedParamQueryScriptWithSingleCondition(
						tableName, selectFieldsVector, null);
	}
	
	private String createDeleteByTerminalSqlScript() {
		return 
			SqlScriptUtils.createNamedParamDeleteScript(
					tableName, _terminalId);
	}
	
	@Override
	public BasicTerminalInfoBean findTerminalBasicInfoByTerminalId(
			String terminalId) throws DataAccessException {
		try {
			return
				super.namedParameterJdbcTemplate.queryForObject(
						this.findTerminalBasicByTerminalIdSqlScript,
						Collections.singletonMap((String)_terminalId, (Object)terminalId),
						new RowMapper<BasicTerminalInfoBean>() {
							public BasicTerminalInfoBean mapRow(ResultSet rs, int rowNum) throws SQLException {
								BasicTerminalInfoBean basicTerminalInfoBean = new BasicTerminalInfoBean();
							
								basicTerminalInfoBean.setTerminalId(rs.getString(_terminalId).trim());
								String clientType = rs.getString(_clientType);
								if (null != clientType) {
									basicTerminalInfoBean.setClientType(rs.getString(_clientType).trim());
								}
								basicTerminalInfoBean.setEffectiveStartTime(
											new Date(rs.getTimestamp(_effectiveStartTime).getTime()));
								
								Timestamp effectiveEndTime = rs.getTimestamp(_effectiveEndTime);
								if (null != effectiveEndTime) {
									basicTerminalInfoBean.setEffectiveEndTime(
												new Date(effectiveEndTime.getTime()));
								}
								
								return basicTerminalInfoBean;
							}
						}
					);
		} catch (EmptyResultDataAccessException e) {
			return null;
		} catch (DataAccessException e) {
			throw e;
		}
	}

	@Override
	public List<BasicTerminalInfoBean> findAllTerminalsBasicInfo() throws DataAccessException {
		JdbcOperations jdbcOperations = super.namedParameterJdbcTemplate.getJdbcOperations();

		return
			jdbcOperations.query(
					this.findAllTerminalBasicSqlScript,
					new RowMapper<BasicTerminalInfoBean>() {
						public BasicTerminalInfoBean mapRow(ResultSet rs, int rowNum) throws SQLException {
							BasicTerminalInfoBean basicTerminalInfoBean = new BasicTerminalInfoBean();
							
							basicTerminalInfoBean.setTerminalId(rs.getString(_terminalId).trim());
							String clientType = rs.getString(_clientType);
							if (null !=  clientType) {
								basicTerminalInfoBean.setClientType(rs.getString(_clientType).trim());
							}
							basicTerminalInfoBean.setEffectiveStartTime(
										new Date(rs.getTimestamp(_effectiveStartTime).getTime()));
							
							Timestamp effectiveEndTime = rs.getTimestamp(_effectiveEndTime);
							if (null != effectiveEndTime) {
								basicTerminalInfoBean.setEffectiveEndTime(new Date(effectiveEndTime.getTime()));
							}
							
						return basicTerminalInfoBean;
						}
					  }
					);
	}
	
	@Override
	public int insertBasic(Map<String, Object> insertValuesMap)
			throws DataAccessException {
		if (null != insertValuesMap && !insertValuesMap.isEmpty()) {
			return
			super.namedParameterJdbcTemplate.update(this.insertBasicSqlScript,
													insertValuesMap);
		}
		else {
			return 0;
		}
	}
	@Override
	public int deleteByTerminalId(String terminalId) throws DataAccessException {
		if (null != terminalId) {
			Map<String,Object> deleteConditionValueMap
						= Collections.singletonMap(_terminalId,
												   (Object)terminalId);
			return
				super.namedParameterJdbcTemplate.
								update(this.deleteByTerminalSqlScript,
									   deleteConditionValueMap);
		}
		else {
			return 0;
		}
	}

	@Override
	public String getTableName() {
		return this.tableName;
	}

}
